from datetime import datetime
import json
from sqlalchemy import text
from sqlalchemy.exc import PendingRollbackError


def get2DateDays(date_str1,date_str2):
    # 将字符串转换为datetime对象
    date1 = datetime.strptime(date_str1, "%Y-%m-%d")
    date2 = datetime.strptime(date_str2, "%Y-%m-%d")
    # 计算两个日期之间的天数差
    days_difference = (date2 - date1).days
    return days_difference


def kpi(request,session):
    result = {
        "code": 200,
        "message": "trigger",
        "data": {
            "total_vehicles": "",
            "mileages": {  # 智驾里程
                "total_mileage": "",  # 总里程数 包含智驾和非智驾
                "total_intelligent_driving_mileage": "",  # 智驾总里程数
                "total_intelligent_driving_mileage_change_rate": "",  # 智驾总里程数增减百分比, 正数为增加，负数为减少
                "detail_intelligent_driving": {
                    "acc": "",  # 自适应巡航
                    "ica": "",  # 智能巡航
                    "hnop": "",  # 高速智驾
                    "unop": "",  # 低速智驾
                }
            },
            "preference": {  # 智驾偏好
                "apa_times_rate_rate": "",  # apa激活次数占比
                "apa_activate_vehicle": "",  # apa激活车数量
                "apa_activate_vehicle_change": "",  # apa激活车辆与上一周期对比变化数量，正数为增加，负数为减少
                "rpa_times_rate_rate": "",  # rpa激活次数占比
                "rpa_activate_vehicle": "",  # rpa激活车数量
                "rpa_activate_vehicle_change": "",  # rpa激活车辆与上一周期对比变化数量，正数为增加，负数为减少
                "vpa_times_rate_rate": "",  # vpa激活次数占比(暂时没有值)
                "vpa_activate_vehicle": "",  # vpa激活车数量(暂时没有值)
                "vpa_activate_vehicle_change": "",  # vpa激活车辆与上一周期对比变化数量，正数为增加，负数为减少(暂时没有值)
            },
            "total_intelligent_driving_vehicles": "",  # 智驾激活车辆总数，包含所有智驾类型
            "total_intelligent_driving_times": "",  # 智驾激活总次数，包含所有智驾类型
            "total_intelligent_driving_vehicles_change": "",  # 智驾激活车辆总数，包含所有智驾类型
            "total_intelligent_driving_times_change": "",  # 智驾激活总次数，包含所有智驾类型
            "total_parking_intelligent_vehicles": "",  # 自动泊车激活车辆数
            "total_parking_intelligent_vehicles_change": "",  # 自动泊车激活车辆数与上一周期对比，正数为增加，负数为减少
            "day_times_parking_intelligent": "",  # 自动泊车单车日均激活次数，
            "day_times_parking_intelligent_day_times_change": "",  # 自动泊车单车日均激活次数与上一周期对比，正数为增加，负数为减少
            "apa_duration_parking_duration": "",  # apa_单次泊车平均时长 秒
            "apa_duration_parking_duration_change": "",  # apa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            "rpa_duration_parking_duration": "",  # rpa_单次泊车平均时长 秒
            "rpa_duration_parking_duration_change": "",  # rpa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            "vpa_duration_parking_duration": "",  # vpa_单次泊车平均时长 秒
            "vpa_duration_parking_duration_change": "",  # vpa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            # 行车功能指标  表数据
            "driving_function_indicators_data": [
                {
                    "function": "",  # 功能名称
                    "vehicles": "",  # 激活车辆数
                    "activate": "",  # 激活次数
                    "avg_mileage": "",  # 单次平均里程
                    "activate_rate": []
                }
            ],
            # 泊车功能指标
            "parking_function_indicators_data": [
                {
                    "function": "",  # 功能名称（VPA，APA，RPA）
                    "avg_duration": "",  # 单次泊车平均时长
                    "activate": "",  # 激活次数
                    "success_rate": "",  # 成功率
                }
            ]
        }
    }
    start_time = request.json["start_time"]
    end_time = request.json["end_time"]
    start_time_pre = request.json["start_time_pre"]
    end_time_pre = request.json["end_time_pre"]
    try:
        # with table_17 as (
        #         select
        # CONVERT(REPLACE(ekt.Total_odo, '"', ''), FLOAT4) as Total_odo, DATE_FORMAT(FROM_UNIXTIME(ekt.ts / 1000), '%Y-%m-%d') as data_temp
        # from explor_kpi_tid_100017 ekt
        # )
        # select
        # IFNULL(round(sum(t17.Total_odo), 2), 0) as sum_total_odo
        # from table_17 t17
        # where
        # t17.data_temp >= '{start_time}' and t17.data_temp <= '{end_time}'
        # 总里程数
        sql1 = f"""
                select ifnull(sum(data_temp.max_total)-sum(data_temp.min_total),0),ifnull(count(data_temp.vid),0) from (
                SELECT 
                   ekt.vid,
				   max(ekt.vs) as ota_version, 
				   max(ekt.Total_odo) as max_total,
				   min(case when ekt.s_odo = 0 then null else ekt.s_odo end ) as min_total
                FROM 
                    explor_kpi_tid_100017 ekt
                WHERE 
                    DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') >=  '{start_time}' 
                    and DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') <=  '{end_time}'
                GROUP BY ekt.vid
                    ) as data_temp
                WHERE (data_temp.ota_version like '%WIN2.0%OTA5%' or data_temp.ota_version like '%WIN2.0%OTA6%')
                """
        # 智驾总里程数
        sql2 = f"""
                with 
                table_2 as(
                select (ekt.f_odo - ekt.s_odo) as odo, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp
                from explor_kpi_tid_100002 ekt
                ),
                table_3 as(
                select (ekt.f_odo - ekt.s_odo) as odo, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp
                from explor_kpi_tid_100003 ekt
                ),
                table_6 as(
                select (ekt.f_odo - ekt.s_odo) as odo, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp
                from explor_kpi_tid_100006 ekt
                ),
                table_18 as(
                select (ekt.f_odo - ekt.s_odo) as odo, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp
                from explor_kpi_tid_100018 ekt
                ),
                total_2 as(
                 select sum(t2.odo) as odo_total from table_2 t2 where t2.data_temp >= '{start_time}' and t2.data_temp <= '{end_time}'
                ),
                total_3 as(
                 select sum(t3.odo) as odo_total from table_3 t3 where t3.data_temp >= '{start_time}' and t3.data_temp <= '{end_time}'
                ),
                total_6 as(
                 select sum(t6.odo) as odo_total from table_6 t6 where t6.data_temp >= '{start_time}' and t6.data_temp <= '{end_time}'
                ),
                total_18 as(
                 select sum(t18.odo) as odo_total from table_18 t18 where t18.data_temp >= '{start_time}' and t18.data_temp <= '{end_time}'
                ),
                total_2_pre as(
                 select sum(t2.odo) as odo_total from table_2 t2 where t2.data_temp >= '{start_time_pre}' and t2.data_temp <= '{end_time_pre}'
                ),
                total_3_pre as(
                 select sum(t3.odo) as odo_total from table_3 t3 where t3.data_temp >= '{start_time_pre}' and t3.data_temp <= '{end_time_pre}'
                ),
                total_6_pre as(
                 select sum(t6.odo) as odo_total from table_6 t6 where t6.data_temp >= '{start_time_pre}' and t6.data_temp <= '{end_time_pre}'
                ),
                total_18_pre as(
                 select sum(t18.odo) as odo_total from table_18 t18 where t18.data_temp >= '{start_time_pre}' and t18.data_temp <= '{end_time_pre}'
                )
                select 
                (IFNULL(to2.odo_total,0)+IFNULL(to3.odo_total,0)+IFNULL(to6.odo_total,0)+IFNULL(to18.odo_total,0)) as total,
                (IFNULL(to2_pre.odo_total,0)+IFNULL(to3_pre.odo_total,0)+IFNULL(to6_pre.odo_total,0)+IFNULL(to18_pre.odo_total,0)) as total_pre,
                IFNULL(to2.odo_total,0) total_ACC,IFNULL(to3.odo_total,0) total_ICA,IFNULL(to6.odo_total,0) total_HNOP,IFNULL(to18.odo_total,0) total_UNOP
                from total_2 to2 , total_3 to3 , total_6 to6 , total_18 to18
                , total_2_pre to2_pre , total_3_pre to3_pre , total_6_pre to6_pre , total_18_pre to18_pre
                """
        # 车辆总数
        sql3 = f"""
                with 
                vid_num_2 as(
                select ekt.vid as vid_2,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') as date_temp from explor_kpi_tid_100002 ekt
                union 
                select ekt.vid as vid_2,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') as date_temp from explor_kpi_tid_100003 ekt
                union
                select ekt.vid as vid_2,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') as date_temp from explor_kpi_tid_100006 ekt
                union
                select ekt.vid as vid_2,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') as date_temp from explor_kpi_tid_100009 ekt
                union
                select ekt.vid as vid_2,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') as date_temp from explor_kpi_tid_100017 ekt
                union
                select ekt.vid as vid_2,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') as date_temp from explor_kpi_tid_100018 ekt
                union
                select ekt.vid as vid_2,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d') as date_temp from explor_kpi_tid_100050 ekt
                ),
                all_vin as(
                select vn.vid_2 vehicle from vid_num_2 vn 
                where vn.date_temp >= '{start_time}' and vn.date_temp <= '{end_time}'
                group by vn.vid_2
                )
                select IFNULL(count(av.vehicle),0) nums from all_vin av
                """
        # APA激活车辆 触发100009埋点就代表APA激活   /  RPA（100050）激活车辆： 车辆触发100050埋点就代表RPA激活。
        sql4 = f"""
                with apa_data as(
                select ekt.vid,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp
                from explor_kpi_tid_100009 ekt 
                ),
                rpa_data as(
                select ekt.vid,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp
                from explor_kpi_tid_100050 ekt 
                ),
                vpa_data as(
                select ekt.vid,DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp
                from explor_kpi_tid_100020 ekt 
                ),
                apa_data_num as(
                select COUNT(DISTINCT (ad.vid)) vin_apa from apa_data ad
                where ad.data_temp >= '{start_time}' and ad.data_temp <= '{end_time}'
                ),
                rpa_data_num as(
                select COUNT(DISTINCT (rd.vid)) vin_rpa from rpa_data rd
                where rd.data_temp >= '{start_time}' and rd.data_temp <= '{end_time}'
                ),
                vpa_data_num as(
                select COUNT(DISTINCT (vd.vid)) vin_vpa from vpa_data vd
                where vd.data_temp >= '{start_time}' and vd.data_temp <= '{end_time}'
                ),
                apa_data_num_pre as(
                select COUNT(DISTINCT (ad.vid)) vin_apa_pre from apa_data ad
                where ad.data_temp >= '{start_time_pre}' and ad.data_temp <= '{end_time_pre}'
                ),
                rpa_data_num_pre as(
                select COUNT(DISTINCT (rd.vid)) vin_rpa_pre from rpa_data rd
                where rd.data_temp >= '{start_time_pre}' and rd.data_temp <= '{end_time_pre}'
                ),
                vpa_data_num_pre as(
                select COUNT(DISTINCT (vd.vid)) vin_vpa_pre from vpa_data vd
                where vd.data_temp >= '{start_time_pre}' and vd.data_temp <= '{end_time_pre}'
                )
                select IFNULL(adn.vin_apa,0) apa_num,IFNULL(adnp.vin_apa_pre,0) apa_pre_num,
                IFNULL(rdn.vin_rpa,0) rpa_num,IFNULL(rdnp.vin_rpa_pre,0) rpa_pre_num,
                IFNULL(vdn.vin_vpa,0) vpa_num,IFNULL(vdnp.vin_vpa_pre,0) vpa_pre_num
                from apa_data_num adn,apa_data_num_pre adnp,
                rpa_data_num rdn,rpa_data_num_pre rdnp,
                vpa_data_num vdn,vpa_data_num_pre vdnp
                """
        sql5 = f"""
                with total_intelligent as(
                    select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100002 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time}' 
                    union all
                    select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100003 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time}' 
                    union all
                    select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100006 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time}' 
                    union all
                    select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100018 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time}' 	
                ),
                total_intelligent_change as(
                select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100002 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time_pre}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time_pre}' 
                    union all
                    select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100003 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time_pre}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time_pre}' 
                    union all
                    select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100006 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time_pre}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time_pre}' 
                    union all
                    select vid, DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') as data_temp from explor_kpi_tid_100018 where DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') >= '{start_time_pre}' and DATE_FORMAT(FROM_UNIXTIME(ts/1000),'%Y-%m-%d') <= '{end_time_pre}' 	
                ),
                total_intelligent_all as(
                    select
                        IFNULL(count(vid),0) all_vchicle,
                        IFNULL(sum(cnt),0) all_times
                    from
                    (
                        select
                            vid,count(vid) cnt
                        from	
                            total_intelligent
                        group by
                            vid
                    )t1
                ),
                total_intelligent_pre as(
                    select
                        IFNULL(count(vid),0) all_vchicle_pre,
                        IFNULL(sum(cnt),0) all_times_pre
                    from
                    (
                        select
                            vid,count(vid) cnt
                        from	
                            total_intelligent_change
                        group by
                            vid
                    )t1
                )
                select
                ifnull(all_times,0),
                ifnull(all_vchicle,0),
                ifnull(all_times_pre,0),
                ifnull(all_vchicle_pre,0)
                from
                total_intelligent_all
                left join 
                total_intelligent_pre
                on 1 = 1
                """
        # 自动泊车单车日均激活次数
        sql6 = f"""
                with 
                    table_9 as(
                    select ekt.vid,CONVERT(REPLACE(ekt.pk_dur, '"', ''),signed) as dur9, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp
                    ,if(ekt.sucs_fl = 1 or ekt.sucs_fl like '%true%',1,0) sucs_9
                    from explor_kpi_tid_100009 ekt
                    ),
                    table_50 as(
                    select ekt.vid,CONVERT(REPLACE(ekt.pk_dur, '"', ''),signed) as dur50, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp
                    ,if(ekt.sucs_fl = 1 or ekt.sucs_fl like '%true%',1,0) sucs_50
                    from explor_kpi_tid_100050 ekt
                    ),
                    table_20 as(
                    select ekt.vid,CONVERT(REPLACE(ekt.pk_dur, '"', ''),signed) as dur20, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp
                    ,if(ekt.sucs_fl = 1 or ekt.sucs_fl like '%true%',1,0) sucs_20
                    from explor_kpi_tid_100020 ekt
                    ),
                    suc9_info as(
                    select avg(t9.dur9) avg9,count(t9.vid) suc_count9 from table_9 t9 where t9.sucs_9 = 1
                     and t9.date_temp >='{start_time}' and t9.date_temp <='{end_time}'
                    ),
                    suc50_info as(
                    select avg(t50.dur50) avg50,count(t50.vid) suc_count50 from table_50 t50 where t50.sucs_50 = 1
                     and t50.date_temp >='{start_time}' and t50.date_temp <='{end_time}'
                    ),
                    suc20_info as(
                    select avg(t20.dur20) avg20,count(t20.vid) suc_count20 from table_20 t20 where t20.sucs_20 = 1
                     and t20.date_temp >='{start_time}' and t20.date_temp <='{end_time}'
                    ),
                    total9_info as (
                    select count(t9.vid) total_count9 from table_9 t9
                     where t9.date_temp >='{start_time}' and t9.date_temp <='{end_time}'
                    ),
                    total50_info as(
                    select count(t50.vid) total_count50 from table_50 t50
                     where t50.date_temp >='{start_time}' and t50.date_temp <='{end_time}'
                    ),
                    total20_info as(
                    select count(t20.vid) total_count20 from table_20 t20
                     where t20.date_temp >='{start_time}' and t20.date_temp <='{end_time}'
                    ),
                    vehicles as (
                        select count(vid) counts from (
                        select DISTINCT t9.vid from table_9 t9 where t9.date_temp >='{start_time}' and t9.date_temp <='{end_time}'
                        union select DISTINCT t50.vid from table_50 t50 where t50.date_temp >='{start_time}' and t50.date_temp <='{end_time}'
                        union select DISTINCT t20.vid from table_20 t20 where t20.date_temp >='{start_time}' and t20.date_temp <='{end_time}'
                        ) t
                    )
                    select vehicles.counts,
                             ifnull(round(suc9_info.avg9/1000,0),0) apa_dur,
                             ifnull(suc9_info.suc_count9,0) apa_times,
                             ifnull(ROUND(suc9_info.suc_count9/total9_info.total_count9*100,1),0) suc_rate_apa,
                             ifnull(round(suc50_info.avg50/1000,0),0) rpa_dur,
                             ifnull(suc50_info.suc_count50,0) rpa_times, 
                             ifnull(ROUND(suc50_info.suc_count50/total50_info.total_count50*100,1),0) suc_rate_rpa,
                             ifnull(round(suc20_info.avg20/1000,0),0) vpa_dur,
                             ifnull(suc20_info.suc_count20,0) vpa_time,
                             ifnull(ROUND(suc20_info.suc_count20/total20_info.total_count20*100,1),0) suc_rate_vpa,
                             ifnull(total9_info.total_count9,0),
                             ifnull(total50_info.total_count50,0),
                             ifnull(total20_info.total_count20,0)
                    from vehicles,suc9_info,suc50_info,suc20_info,total9_info,total50_info,total20_info
                """
        sql6_pre = f"""
                        with 
                            table_9 as(
                            select ekt.vid,CONVERT(REPLACE(ekt.pk_dur, '"', ''),signed) as dur9, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp
                            ,if(ekt.sucs_fl = 1 or ekt.sucs_fl like '%true%',1,0) sucs_9
                            from explor_kpi_tid_100009 ekt
                            ),
                            table_50 as(
                            select ekt.vid,CONVERT(REPLACE(ekt.pk_dur, '"', ''),signed) as dur50, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp
                            ,if(ekt.sucs_fl = 1 or ekt.sucs_fl like '%true%',1,0) sucs_50
                            from explor_kpi_tid_100050 ekt
                            ),
                            table_20 as(
                            select ekt.vid,CONVERT(REPLACE(ekt.pk_dur, '"', ''),signed) as dur20, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as date_temp
                            ,if(ekt.sucs_fl = 1 or ekt.sucs_fl like '%true%',1,0) sucs_20
                            from explor_kpi_tid_100020 ekt
                            ),
                            suc9_info as(
                            select avg(t9.dur9) avg9,count(t9.vid) suc_count9 from table_9 t9 where t9.sucs_9 = 1
                            and t9.date_temp >='{start_time_pre}' and t9.date_temp <='{end_time_pre}'
                            ),
                            suc50_info as(
                            select avg(t50.dur50) avg50,count(t50.vid) suc_count50 from table_50 t50 where t50.sucs_50 = 1
                             and t50.date_temp >='{start_time_pre}' and t50.date_temp <='{end_time_pre}'
                            ),
                            suc20_info as(
                            select avg(t20.dur20) avg20,count(t20.vid) suc_count20 from table_20 t20 where t20.sucs_20 = 1
                             and t20.date_temp >='{start_time_pre}' and t20.date_temp <='{end_time_pre}'
                            ),
                            total9_info as (
                            select count(t9.vid) total_count9 from table_9 t9
                             where t9.date_temp >='{start_time_pre}' and t9.date_temp <='{end_time_pre}'
                            ),
                            total50_info as(
                            select count(t50.vid) total_count50 from table_50 t50
                             where t50.date_temp >='{start_time_pre}' and t50.date_temp <='{end_time_pre}'
                            ),
                            total20_info as(
                            select count(t20.vid) total_count20 from table_20 t20
                             where t20.date_temp >='{start_time_pre}' and t20.date_temp <='{end_time_pre}'
                            ),
                            vehicles as (
                                select count(vid) counts from (
                                select DISTINCT t9.vid from table_9 t9 where t9.date_temp >='{start_time_pre}' and t9.date_temp <='{end_time_pre}'
                                union select DISTINCT t50.vid from table_50 t50 where t50.date_temp >='{start_time_pre}' and t50.date_temp <='{end_time_pre}'
                                union select DISTINCT t20.vid from table_20 t20 where t20.date_temp >='{start_time_pre}' and t20.date_temp <='{end_time_pre}'
                                ) t
                            )
                            select vehicles.counts,
                             ifnull(round(suc9_info.avg9/1000,0),0) apa_dur,
                             ifnull(suc9_info.suc_count9,0) apa_times,
                             ifnull(ROUND(suc9_info.suc_count9/total9_info.total_count9*100,1),0) suc_rate_apa,
                             ifnull(round(suc50_info.avg50/1000,0),0) rpa_dur,
                             ifnull(suc50_info.suc_count50,0) rpa_times, 
                             ifnull(ROUND(suc50_info.suc_count50/total50_info.total_count50*100,1),0) suc_rate_rpa,
                             ifnull(round(suc20_info.avg20/1000,0),0) vpa_dur,
                             ifnull(suc20_info.suc_count20,0) vpa_time,
                             ifnull(ROUND(suc20_info.suc_count20/total20_info.total_count20*100,1),0) suc_rate_vpa,
                             ifnull(total9_info.total_count9,0),
                             ifnull(total50_info.total_count50,0),
                             ifnull(total20_info.total_count20,0)
                            from vehicles,suc9_info,suc50_info,suc20_info,total9_info,total50_info,total20_info
                        """
        sql7 = f"""
                with 
                table_2 as(
                select ekt.vid as vid_2,(ekt.f_odo - ekt.s_odo) as odo_2, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp_2
                from explor_kpi_tid_100002 ekt
                ),
                table_3 as(
                select ekt.vid as vid_3,(ekt.f_odo - ekt.s_odo) as odo_3, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp_3
                from explor_kpi_tid_100003 ekt
                ),
                table_6 as(
                select ekt.vid as vid_6,(ekt.f_odo - ekt.s_odo) as odo_6, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp_6
                from explor_kpi_tid_100006 ekt
                ),
                table_18 as(
                select ekt.vid as vid_18,(ekt.f_odo - ekt.s_odo) as odo_18, DATE_FORMAT(FROM_UNIXTIME(ekt.ts/1000),'%Y-%m-%d')  as data_temp_18
                from explor_kpi_tid_100018 ekt
                ),
                total_2 as(
                 select * from table_2 t2 where t2.data_temp_2 >= '{start_time}' and t2.data_temp_2 <= '{end_time}'
                ),
                total_3 as(
                 select * from table_3 t3 where t3.data_temp_3 >= '{start_time}' and t3.data_temp_3 <= '{end_time}'
                ),
                total_6 as(
                 select * from table_6 t6 where t6.data_temp_6 >= '{start_time}' and t6.data_temp_6 <= '{end_time}'
                ),
                total_18 as(
                 select * from table_18 t18 where t18.data_temp_18 >= '{start_time}' and t18.data_temp_18 <= '{end_time}'
                )
                ,
                vid_2_times as (select count(to2.vid_2) vid_2_times
                from total_2 to2
                ),
                vid_3_times as (select count(to3.vid_3) vid_3_times
                from total_3 to3
                ),
                vid_6_times as (select count(to6.vid_6) vid_6_times
                from total_6 to6
                ),
                vid_18_times as (select count(to18.vid_18) vid_18_times
                from total_18 to18
                ),
                vid_2_vchicle as(
                select count(1) vid_2_vchicle from (select to2.vid_2
                from total_2 to2 group by to2.vid_2) vid_2_total
                ),
                vid_3_vchicle as(
                select count(1) vid_3_vchicle from (select to3.vid_3
                from total_3 to3 group by to3.vid_3) vid_3_total
                ),
                vid_6_vchicle as(
                select count(1) vid_6_vchicle from (select to6.vid_6
                from total_6 to6 group by to6.vid_6) vid_6_total
                ),
                vid_18_vchicle as(
                select count(1) vid_18_vchicle from (select to18.vid_18
                from total_18 to18 group by to18.vid_18) vid_18_total
                )
                ,
                vid_2_len as(
                select sum(tt2.odo_2) as vid_2_len from total_2 tt2
                ),
                vid_3_len as(
                select sum(tt3.odo_3) as vid_3_len from total_3 tt3
                ),
                vid_6_len as(
                select sum(tt6.odo_6) as vid_6_len from total_6 tt6
                ),
                vid_18_len as(
                select sum(tt18.odo_18) as vid_18_len from total_18 tt18
                ),
                time_2_list as(
                select count(to2.vid_2) time_2_list from total_2 to2 group by to2.data_temp_2 order by to2.data_temp_2 asc
                ),
                time_3_list as(
                select count(to3.vid_3) time_3_list from total_3 to3 group by to3.data_temp_3 order by to3.data_temp_3 asc
                ),
                time_6_list as(
                select count(to6.vid_6) time_6_list from total_6 to6 group by to6.data_temp_6 order by to6.data_temp_6 asc
                ),
                time_18_list as(
                select count(to18.vid_18) time_18_list from total_18 to18 group by to18.data_temp_18 order by to18.data_temp_18 asc
                )
                select IFNULL(vid_2_times,0),
                IFNULL(vid_3_times,0),
                IFNULL(vid_6_times,0),
                IFNULL(vid_18_times,0),
                IFNULL(vid_2_vchicle,0),
                IFNULL(vid_3_vchicle,0),
                IFNULL(vid_6_vchicle,0),
                IFNULL(vid_18_vchicle,0),
                IFNULL(round((vid_2_len/(CASE when vid_2_times = 0 OR vid_2_times IS NULL THEN 1 else vid_2_times end)),3),0) as len_avg_2,
                IFNULL(round((vid_3_len/(CASE when vid_3_times = 0 OR vid_3_times IS NULL THEN 1 else vid_3_times end)),3),0) as len_avg_3,
                IFNULL(round((vid_6_len/(CASE when vid_6_times = 0 OR vid_6_times IS NULL THEN 1 else vid_6_times end)),3),0) as len_avg_6,
                IFNULL(round((vid_18_len/(CASE when vid_18_times = 0 OR vid_18_times IS NULL THEN 1 else vid_18_times end)),3),0) as len_avg_18
                ,IFNULL((select GROUP_CONCAT(time_2_list)  from time_2_list),0) as time_2_list
                ,IFNULL((select GROUP_CONCAT(time_3_list)  from time_3_list),0) as time_3_list
                ,IFNULL((select GROUP_CONCAT(time_6_list)  from time_6_list),0) as time_6_list
                ,IFNULL((select GROUP_CONCAT(time_18_list)  from time_18_list),0) as time_18_list
                from vid_2_times,vid_3_times,vid_6_times,vid_18_times,vid_2_vchicle,vid_3_vchicle,vid_6_vchicle,vid_18_vchicle
                ,vid_2_len,vid_3_len,vid_6_len,vid_18_len
                """
        sql8 = f"""
                WITH daily_counts AS (
                SELECT 
                    DATE(FROM_UNIXTIME(ts / 1000)) AS date, 
                    vid, 
                    COUNT(*) AS daily_count
                FROM 
                    explor_kpi_tid_100009
                GROUP BY 
                    DATE(FROM_UNIXTIME(ts / 1000)), 
                    vid
                UNION ALL
                SELECT 
                    DATE(FROM_UNIXTIME(ts / 1000)) AS date, 
                    vid, 
                    COUNT(*) AS daily_count
                FROM 
                    explor_kpi_tid_100050
                GROUP BY 
                    DATE(FROM_UNIXTIME(ts / 1000)), 
                    vid
                UNION ALL
                SELECT 
                    DATE(FROM_UNIXTIME(ts / 1000)) AS date, 
                    vid, 
                    COUNT(*) AS daily_count
                FROM 
                    explor_kpi_tid_100020
                GROUP BY 
                    DATE(FROM_UNIXTIME(ts / 1000)), 
                    vid
            ),
            daily_averages AS (
                SELECT 
                    date, 
                    SUM(daily_count) / COUNT(date) AS d_avg
                FROM 
                    daily_counts
                GROUP BY 
                    date
            ),
            avg_times as (SELECT avg(d_avg) avg_times
            FROM 
                daily_averages
            where date>='{start_time}' and date <= '{end_time}'
            ),
            avg_times_pre as (
            SELECT avg(d_avg) avg_times_pre
            FROM 
                daily_averages
            where date>='{start_time_pre}' and date <= '{end_time_pre}'
            )
            select round(ifnull(avg_times.avg_times,0),2) times,round(ifnull(avg_times_pre.avg_times_pre,0),2) times_pre,
             round(((ifnull(avg_times.avg_times,0)-ifnull(avg_times_pre.avg_times_pre,0)) / ifnull(avg_times_pre.avg_times_pre,1))*100,2) rate
            from avg_times,avg_times_pre
                """
        result_data_1 = session.execute(text(sql1)).all()
        result_data_2 = session.execute(text(sql2)).all()
        result_data_3 = session.execute(text(sql3)).all()
        result_data_4 = session.execute(text(sql4)).all()
        result_data_5 = session.execute(text(sql5)).all()
        result_data_6 = session.execute(text(sql6)).all()
        result_data_6_pre = session.execute(text(sql6_pre)).all()
        result_data_7 = session.execute(text(sql7)).all()
        result_data_8 = session.execute(text(sql8)).all()
        # print(1,result_data_1)
        # print(2,result_data_2)
        # print(3,result_data_3)
        # print(4,result_data_4)
        # print(5,result_data_5)
        # print(6,result_data_6)
        # print(7,result_data_6_pre)
        # print(8,result_data_7)
        # print(9,result_data_8)
        result["data"]["total_vehicles"] = result_data_3[0][0]
        result["data"]["mileages"]["total_mileage"] = float(format(result_data_1[0][0], '.2f'))
        result["data"]["mileages"]["total_intelligent_driving_mileage"] = float(format(result_data_2[0][0], '.2f'))
        result["data"]["mileages"]["total_intelligent_driving_mileage_change_rate"] = float(format(result_data_2[0][1], '.2f'))
        result["data"]["mileages"]["detail_intelligent_driving"]["acc"] = float(format(result_data_2[0][2], '.2f'))
        result["data"]["mileages"]["detail_intelligent_driving"]["ica"] = float(format(result_data_2[0][3], '.2f'))
        result["data"]["mileages"]["detail_intelligent_driving"]["hnop"] = float(format(result_data_2[0][4], '.2f'))
        result["data"]["mileages"]["detail_intelligent_driving"]["unop"] = float(format(result_data_2[0][5], '.2f'))

        result["data"]["preference"]["apa_times_rate_rate"] = 0
        result["data"]["preference"]["apa_activate_vehicle"] = result_data_4[0][0]
        result["data"]["preference"]["apa_activate_vehicle_change"] = result_data_4[0][0] - result_data_4[0][1]
        result["data"]["preference"]["rpa_times_rate_rate"] = 0
        result["data"]["preference"]["rpa_activate_vehicle"] = result_data_4[0][2]
        result["data"]["preference"]["rpa_activate_vehicle_change"] = result_data_4[0][2] - result_data_4[0][3]
        result["data"]["preference"]["vpa_times_rate_rate"] = 0
        result["data"]["preference"]["vpa_activate_vehicle"] = result_data_4[0][4]
        result["data"]["preference"]["vpa_activate_vehicle_change"] = result_data_4[0][4] - result_data_4[0][5]

        result["data"]["total_intelligent_driving_vehicles"] = result_data_5[0][1]
        result["data"]["total_intelligent_driving_times"] = float(format(result_data_5[0][0], '.0f'))
        result["data"]["total_intelligent_driving_vehicles_change"] = result_data_5[0][1] - result_data_5[0][3]
        # times_ai = result_data_5[0][0]
        # times_ai_pre = result_data_5[0][2]
        # rate_times_change = float(format((times_ai - times_ai_pre) / (times_ai_pre if times_ai_pre > 0 else 1) * 100,
        #                                  '.0f'))
        result["data"]["total_intelligent_driving_times_change"] = int(result_data_5[0][2])

        result["data"]["total_parking_intelligent_vehicles"] = int(result_data_6[0][0])
        result["data"]["total_parking_intelligent_vehicles_change"] = result_data_6[0][0] - result_data_6_pre[0][0]
        # result["data"]["day_times_parking_intelligent"] = result_data_6[0][0]

        # times_parking = result_data_6[0][2]
        # times_vehicles = result_data_6[0][0]
        # times_parking_pre = result_data_6_pre[0][2]
        # times_vehicles_pre = result_data_6_pre[0][0]
        # days = get2DateDays(start_time, end_time) + 1
        # every_vehicle_day = times_parking / days / (times_vehicles if times_vehicles > 0 else 1)
        # every_vehicle_day_pre = times_parking_pre / days / (times_vehicles_pre if times_vehicles_pre > 0 else 1)
        # rate_parking_change = float(format((every_vehicle_day - every_vehicle_day_pre) /
        #                                    (every_vehicle_day_pre if every_vehicle_day_pre > 0 else 1) * 100, '.0f'))
        # rate_parking_change = float(format((times_parking - times_parking_pre) /
        #                              (times_parking_pre if times_parking_pre > 0 else 1) * 100, '.0f'))
        result["data"]["day_times_parking_intelligent"] = float(result_data_8[0][0])
        result["data"]["day_times_parking_intelligent_day_times_change"] = float(result_data_8[0][1])
        result["data"]["apa_duration_parking_duration"] = float(result_data_6[0][1])
        result["data"]["apa_duration_parking_duration_change"] = float(format(
            (result_data_6[0][1] - result_data_6_pre[0][1]),
            '.0f'))
        result["data"]["rpa_duration_parking_duration"] = float(result_data_6[0][4])
        result["data"]["rpa_duration_parking_duration_change"] = float(format(
            (result_data_6[0][4] - result_data_6_pre[0][4]),
            '.0f'))
        result["data"]["vpa_duration_parking_duration"] = float(result_data_6[0][7])
        result["data"]["vpa_duration_parking_duration_change"] = float(format(
            (result_data_6[0][7] - result_data_6_pre[0][7]),
            '.0f'))

        result["data"]["driving_function_indicators_data"] = [
            {
                "function": "ACC", "vehicles": result_data_7[0][4], "activate": result_data_7[0][0],
                "avg_mileage": float(format(result_data_7[0][8], '.3f')), "activate_rate": result_data_7[0][12]
            }, {
                "function": "TJA/ICA", "vehicles": result_data_7[0][5], "activate": result_data_7[0][1],
                "avg_mileage": float(format(result_data_7[0][9], '.3f')), "activate_rate": result_data_7[0][13]
            }, {
                "function": "HNOP", "vehicles": result_data_7[0][6], "activate": result_data_7[0][2],
                "avg_mileage": float(format(result_data_7[0][10], '.3f')), "activate_rate": result_data_7[0][14]
            }, {
                "function": "UNOP", "vehicles": result_data_7[0][7], "activate": result_data_7[0][3],
                "avg_mileage": float(format(result_data_7[0][11], '.3f')), "activate_rate": result_data_7[0][15]
            }
        ]
        result["data"]["parking_function_indicators_data"] = [
            {
                "function": "APA",
                "avg_duration": int(format(result_data_6[0][1], '0.0f')),
                "activate": result_data_6[0][10],
                "success_rate": int(format(result_data_6[0][3], '0.0f')),
            },
            {
                "function": "RPA",
                "avg_duration": int(format(result_data_6[0][4], '0.0f')),
                "activate": result_data_6[0][11],
                "success_rate": int(format(result_data_6[0][6], '0.0f')),
            },
            {
                "function": "VPA",
                "avg_duration": int(format(result_data_6[0][7], '0.0f')),
                "activate": result_data_6[0][12],
                "success_rate": int(format(result_data_6[0][9], '0.0f')),
            }
        ]
        # result["data"]["parking_function_indicators_data_list"] = [
        #     [
        #         int(format(result_data_8[0][1], '0.0f')),
        #         int(result_data_8[0][0]),
        #         int(format(result_data_8[0][2], '0.0f')),
        #     ],
        #     [
        #         int(format(result_data_8[0][4], '0.0f')),
        #         int(result_data_8[0][3]),
        #         int(format(result_data_8[0][5], '0.0f')),
        #     ],
        #     [
        #         int(format(result_data_8[0][7], '0.0f')),
        #         int(result_data_8[0][6]),
        #         int(format(result_data_8[0][8], '0.0f')),
        #     ]
        # ]
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)